﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Collections;


namespace Antu.Business.DmpExcel
{
    /// <summary>
    /// 用流的方式导出EXCEL，包括通用的Excel和特殊样式的Excel
    /// 速度较之前的先生成Excel后下载的方式速度有明显的提升，但样式没有之前的方式好看
    /// 并且传过来的datatable要求字段名全部是将要显示的excel的标题
    /// add by LZW
    /// 修 改 人:LiuY
    /// 修改时间:2009-04-10
    /// 修改描述:
    /// 修改时间:2009-06-26 LiuY
    /// 修改描述:增加隐藏字段
    /// </summary>
    public class DmpExcel
    {
        #region 通过输出流的方式导出Excel
        /// <summary>
        /// 通过缓冲流的方式导出Excel
        /// </summary>
        /// <param name="exlStruct"></param>
        public void ExpExcel(ExcelStruct exlStruct)
        {
            Page pageSource = exlStruct.PageSource;
            StringBuilder strBld = new StringBuilder();
            HttpResponse Rsp = pageSource.Response;
            CreatHttpResponHeader(Rsp, exlStruct.TableName);
            strBld.Append(CreateTableHeader(exlStruct));//添加表头
            strBld.Append(CreateColumnHeader(exlStruct));//添加标题头            
            strBld.Append(CreateTableRows(exlStruct));//添加数据行
            strBld.Append(CreateCollectRow(exlStruct));//添加统计行
            strBld.Append(CreateTableEnd(exlStruct));//添加表尾

            Rsp.Write(strBld.ToString());
            Rsp.End();
        }

        #endregion

        #region 设置输出流HTTP头
        private void CreatHttpResponHeader(HttpResponse Rsponse, string TableName)
        {
            Rsponse.Clear();
            Rsponse.Buffer = true;
            Rsponse.ContentEncoding = System.Text.Encoding.UTF8;//输出流的字符集，使用gb2312老是出现乱码，原因正在查找
            Rsponse.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(TableName + ".xls", Encoding.UTF8));
            Rsponse.ContentType = "application/vnd.ms-excel.numberformat:@";
        }

        #endregion

        #region 创建表头
        private string CreateTableHeader(ExcelStruct Es)
        {
            StringBuilder strTableHeader = new StringBuilder();
            string[] hidCols = new string[0];
            if (!string.IsNullOrEmpty(Es.HiddenField))
            {
                hidCols = Es.HiddenField.Split(',');
            }
            strTableHeader.AppendFormat("<table {0}  style='font-family:宋体;text-align:center;'>", Es.TableCss);
            strTableHeader.AppendFormat(@"<tr style='height:60px'><td colspan='{0}'style='text-align:center;font-size:18pt;font-weight:bold'>{1}</td>
                                          </tr>", Es.Flag == "0" ? (Es.DataTableTemp.Columns.Count - hidCols.Length) : (Es.DataTableTemp.Columns.Count + 1 - hidCols.Length),
                                                Es.TableName);
            return strTableHeader.ToString();
        }
        #endregion

        #region 创建表尾
        //edit by xiaojian 2009-09-19 表尾生成当前时间
        private string CreateTableEnd(ExcelStruct Es)
        {
            string[] hidCols = new string[0];
            if (!string.IsNullOrEmpty(Es.HiddenField))
            {
                hidCols = Es.HiddenField.Split(',');
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("</table>");
            sb.AppendFormat("<table {0}  style='text-align:center;'><tr><td colspan='{1}' style='text-align:right;'>" + DateTime.Now.ToString("yyyy-MM-dd") + "</td></tr></table>", Es.TableCss, Es.Flag == "0" ? (Es.DataTableTemp.Columns.Count - hidCols.Length) : (Es.DataTableTemp.Columns.Count + 1 - hidCols.Length));
            return sb.ToString();
        }

        #endregion

        #region 创建标题头
        /// <summary>
        /// 创建标题头
        /// </summary>
        /// <param name="Es"></param>
        /// <returns></returns>
        private string CreateColumnHeader(ExcelStruct Es)
        {
            if (!string.IsNullOrEmpty(Es.TableHeader))
            {
                return Es.TableHeader;
            }
            DataTable dtTemp = Es.DataTableTemp;
            StringBuilder strColumnHeader = new StringBuilder();
            string strColumn = string.Empty;
            strColumnHeader.AppendFormat("<tr style='height:30px;{0}'>", Es.RowNameBold ? "font-weight:bold" : "");
            if (Es.Flag != "0")
            {
                strColumnHeader.Append("<td style='text-align:center;'>序号</td>");
            }
            foreach (DataColumn col in dtTemp.Columns)
            {
                string strHidColumns = isHidColumns(Es, col.ColumnName);
                if (strHidColumns == "1")
                {
                    strColumnHeader.AppendFormat("<td style='text-align:center;'>{0}</td>", col.ColumnName);
                }
              
            }
            strColumnHeader.Append("</tr>");
            strColumn = strColumnHeader.ToString();
            return strColumn;
        }
        #endregion

        #region 生成数据行
        /// <summary>
        /// 生成数据行
        /// </summary>
        /// <param name="Es"></param>
        /// <returns></returns>
        private string CreateTableRows(ExcelStruct Es)
        {
            StringBuilder strBldRows = new StringBuilder();
            DataTable dtTemp = Es.DataTableTemp;
            DictionaryEntry De = Es.DeRowspan;
            string DeKey = De.Key == null ? "" : De.Key.ToString();
            string DeValue = De.Value == null ? "" : De.Value.ToString();
            string strRows = string.Empty;
            int i = 0;
            foreach (DataRow row in dtTemp.Rows)
            {
                i++;
                int colspan = string.IsNullOrEmpty(DeValue) ? 1 : Convert.ToInt32(DeValue);
                string No = Convert.ToString((i - 1) % colspan);
                strBldRows.Append("<tr>");
                StatNoRow(strBldRows, i, No, Es.Flag, colspan);
                StatRow(strBldRows, No, colspan, dtTemp, DeKey, row, Es);
                strBldRows.Append("</tr>");
            }
            strRows = strBldRows.ToString();
            return strRows;
        }
        #endregion

        #region 隐藏列
        private string isHidColumns(ExcelStruct Es, string strColumn)
        {
            string strFlag = "1";
            if (!string.IsNullOrEmpty(Es.HiddenField))
            {
                string[] hidCols = Es.HiddenField.Split(',');
                for (int i = 0; i < hidCols.Length; i++)
                {
                    if (strColumn == hidCols[i].ToString())
                    {
                        strFlag = "0";
                        break;
                    }
                    else
                    {
                        strFlag = "1";
                    }
                }
            }
            return strFlag;
        }

        #endregion

        #region 数据列
        private void StatRow(StringBuilder strBld, string No, int colspan, DataTable dtTemp, string DeKey, DataRow row, ExcelStruct Es)
        {
            foreach (DataColumn col in dtTemp.Columns)
            {
                string strHidColumns = isHidColumns(Es, col.ColumnName);
                if (col.ColumnName == DeKey)
                {
                    if (No == "0")
                    {
                        strBld.AppendFormat("<td style='text-align:center;' rowspan='{1}'>{0}</td>", RetryColumnStyle(col.ColumnName, row[col].ToString()), colspan);
                    }
                }
                else if (strHidColumns == "1")
                {
                    strBld.AppendFormat("<td style='text-align:left;'>{0}</td>", RetryColumnStyle(col.ColumnName, row[col].ToString()));
                }
            }
        }
        #endregion

        #region 序号列
        private void StatNoRow(StringBuilder strBld, int i, string No, string flag, int colspan)
        {
            switch (flag)
            {
                case "0":
                    break;
                case "1":
                    strBld.AppendFormat("<td style='text-align:center;'>{0}</td>", i.ToString());
                    break;
                case "2":
                    if (No == "0")
                    {
                        strBld.AppendFormat("<td style='text-align:center;'  rowspan='{0}'>{1}</td>", colspan, Convert.ToString(i / colspan + 1));
                    }
                    break;
            }
        }
        #endregion

        #region 添加统计行
        /// <summary>
        /// 添加统计行
        /// </summary>
        /// <param name="Es"></param>
        /// <returns></returns>
        private string CreateCollectRow(ExcelStruct Es)
        {
            StringBuilder strBldCollect = new StringBuilder();

            string strCol = Es.StatField;
            if (string.IsNullOrEmpty(strCol))
            {
                return "";
            }
            string strCollect = string.Empty;
            CollectRow(strBldCollect, Es);
            strCollect = strBldCollect.ToString();
            return strCollect;
        }
        #endregion

        #region 统计行
        private void CollectRow(StringBuilder strBldCollect, ExcelStruct Es)
        {
            string[] cols = Es.StatField.Split(',');
            if (cols.Length > 0)
            {
                strBldCollect.AppendFormat(@"<tr style='font-weight:bold'><td style='text-align:center;'>{0}</td>",
                                             Es.StatColumnName == "" ? "合计" : Es.StatColumnName);
                CollectColumn(strBldCollect, Es);
                strBldCollect.Append("</tr>");
            }
        }
        #endregion

        #region 统计列
        private void CollectColumn(StringBuilder strBldCollect, ExcelStruct Es)
        {
            string i = Es.Flag;
            DataTable dtTemp = Es.DataTableTemp;
            string[] cols = Es.StatField.Split(',');
            foreach (DataColumn column in dtTemp.Columns)
            {
                if (i == "0")
                {
                    i = "1"; continue;
                }
                bool isCollect = false;
                foreach (string col in cols)
                {
                    if (column.ColumnName.ToUpper() == col.ToUpper())
                    {
                        isCollect = true;
                        strBldCollect.AppendFormat("<td style='text-align:left;'>{0}</td>", SumColumn(dtTemp, col, Es.Average, Es.PointCount));
                    }
                }
                if (!isCollect && column.ColumnName.ToUpper() != "IID" && column.ColumnName.ToUpper() != "ID")
                {
                    strBldCollect.Append("<td>&nbsp;</td>");
                }
            }
        }

        #endregion

        #region 统计列值
        /// <summary>
        /// 统计列值
        /// </summary>
        /// <param name="dtTemp">Datable数据源</param>
        /// <param name="column">列</param>
        /// <param name="average">取商分母</param>
        /// <param name="PointCount">小数点位数</param>
        /// <returns></returns>
        private string SumColumn(DataTable dtTemp, string column, int average, int PointCount)
        {
            string strSum = string.Empty;
            double sumCol = 0;
            foreach (DataRow row in dtTemp.Rows)
            {
                string strCol = row[column].ToString().Trim();
                if (strCol == "")
                {
                    strCol = "0";
                }
                sumCol += Math.Round(Convert.ToDouble(strCol) / average, PointCount);
            }
            strSum = sumCol.ToString();
            if (strSum.Length >= 11)
            {
                strSum = "'" + strSum;
            }
            return strSum;

        }
        #endregion

        #region 屏蔽生成的excel的科学计数法
        /// <summary>
        /// 屏蔽生成的excel的科学计数法
        /// </summary>
        /// <param name="colName">字段名</param>
        /// <param name="colValue">字段值</param>
        /// <returns></returns>
        private string RetryColumnStyle(string colName, string colValue)
        {
            string strReturn = colValue;
            if ((colName.ToUpper().IndexOf("IID") >= 0 || colName.ToUpper().IndexOf("ID") >= 0
                || colName.ToUpper().IndexOf("身份证") >= 0 || colName.ToUpper().IndexOf("证件") >= 0
                || colName.ToUpper().IndexOf("收入") >= 0 || colName.ToUpper().IndexOf("电话") >= 0
                || colName.ToUpper().IndexOf("面积") >= 0 || colName.ToUpper().IndexOf("金额") >= 0)
                && colValue.Length >= 12)
            {
                strReturn = "'" + colValue;
            }
            return strReturn;
        }
        #endregion


    }
}
